﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data; // To use this you must add a Refrence to "MySql.Data.dll"
using MySql.Data.MySqlClient; // To use this you must add a Refrence to "MySql.Data.dll"
using INI;

namespace MySQL
{
    public class MySQL
    {
        static IniFile ini = new IniFile(".\\config.ini");
        //static string SQLHost = "192.168.15.103"; // The location of the SQL Server.
        //static string SQLDB = "test"; // The database of tables you want to connect to.
        //static string SQLAdmin = "root"; // The username of the administrator account that will access the SQL Server.
        //static string SQLPass = "bur07nsy"; // The password of the administrator account that will access the SQL Server.

        MySqlConnection MySqlConn = new MySqlConnection(ConnStr()); // Declare a new Instance of MySqlConnection
        MySqlCommand MySqlCmd = new MySqlCommand(); // Declare a new Instance of MySqlCommand (This is the Class for Querys)

        static private string ConnStr()
        {
            StringBuilder SB = new StringBuilder(); // Declare a new Instance of StringBuilder so we can create the Connection String.
            SB.Append("Network Address=" + ini.IniReadValue("auto race", "server") + ";");
            SB.Append("Database='" + ini.IniReadValue("auto race", "database") + "';");
            SB.Append("User Name='" + ini.IniReadValue("auto race", "username") + "';");
            SB.Append("Password='" + ini.IniReadValue("auto race", "password") + "'");
            return SB.ToString(); // Return the whole Connection String as 1 string, not 4 single strings.
        }

        public void ExecuteQuery(string Query) // Usage "ExecuteQuery("INSERT INTO `MyTable` (`Col1` `Col2`) VALUES ('Col1Value', 'Col2Value')");
        {
            MySqlCmd.CommandText = Query; // Set the MySqlCmd's Query to what the user has input.
            MySqlCmd.Connection = MySqlConn; // Set the MySqlCmd's Connection to the SQL Connection.
            MySqlConn.Open(); // Open the MySql Connection.
            try
            {
                MySqlCmd.ExecuteNonQuery(); // Execute the Query.

            }
            catch (Exception ex)
            {
                MySqlConn.Close();
                Console.WriteLine(ex.Message);
                Console.WriteLine(Environment.NewLine);
                Console.WriteLine(ex.Source);
            }
            MySqlConn.Close(); // Close the MySql Connection.
        }

        public string ExecuteReturnQuery(string query) // Usage "ExecuteReturnQuery("SELECT `MyCol1` FROM `MyTable` WHERE (MyValue = WhatIWant)");
        {
            string Result = ""; // Set the Returned string to blank so that if the Query errors, the program wont error.
            MySqlCmd.CommandText = query; // Set the MySqlCmd's Query to what the user has input.
            MySqlCmd.Connection = MySqlConn; // Set the MySqlCmd's Connection to the SQL Connection.
            MySqlConn.Open(); // Open the MySql Connection.
            try
            {
                Result = MySqlCmd.ExecuteScalar().ToString(); // Try to retrieve the value asked for by the Qeury
            }
            catch (Exception ex) // If there is a error do nothing and continue on.
            {
                MySqlConn.Close(); // Close the MySql Connection.
                Console.WriteLine(query);
                Console.WriteLine(Environment.NewLine);
                Console.WriteLine("Please check your database table for properly entered data, or enter data.");
                Console.WriteLine(Environment.NewLine);
                Console.WriteLine(ex.Message);
                Console.WriteLine(Environment.NewLine);
                Console.WriteLine(ex.Source);
            } 
            MySqlConn.Close(); // Close the MySql Connection.
            return Result; // Return the value asked for by the Qeury
        }
    }
}
